package com.google.code.mukiranaproject.persistencia.dao;

import java.sql.SQLException;
import java.text.SimpleDateFormat;

import com.google.code.mukiranaproject.model.Lance;
import com.google.code.mukiranaproject.persistencia.Conexao;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;

public class LanceDao extends Conexao {
	
	
	public void selecionarLance(){
		try {
			PreparedStatement pstmt = (PreparedStatement) getConnection().prepareStatement("select * from LANCE");
			ResultSet rs = (ResultSet) pstmt.executeQuery();
			while(rs.next()){
				System.out.println("id_leilao:" + rs.getInt(1) + "\tvalor:"+rs.getInt(2)+"\tdata:"+rs.getDate(3)+"\tusuario:"+rs.getString(4));
				
				SimpleDateFormat formato = new SimpleDateFormat("dd/MM/yy hh:mm:ss");   
				System.out.println(formato.format(rs.getDate(3)));   
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void incluirLance(Lance lance){
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" insert into LANCE ( id_leilao, valor, data_lance, id_usuario ) ");
			sb.append(" values( ?, ?, ?, ? ) ");
			
			PreparedStatement pstmt = (PreparedStatement) getConnection().prepareStatement(sb.toString());
			
			pstmt.setString(1, lance.getLeilao());
			pstmt.setInt(2, lance.getCentavos().intValue());
			pstmt.setDate(3, null);
			pstmt.setString(4, lance.getUsuario());
			
			pstmt.execute();
			
			pstmt.close();
			this.fecharConexao();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public boolean lanceExistente(Lance lance){
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) getConnection().prepareStatement("select * from LANCE where id_leilao = ? and valor = ?");
			pstmt.setString(1, lance.getLeilao());
			pstmt.setInt(2, lance.getCentavos().intValue());
			ResultSet rs = (ResultSet) pstmt.executeQuery();
			
			boolean retorno = rs.next();
			
			rs.close();
			pstmt.close();
			fecharConexao();
			return retorno;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	public void contabilizarUltimosLances(int qtd){
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) getConnection().prepareStatement("select id_usuario from LANCE order by valor desc limit ?");
			pstmt.setInt(1, qtd);
			ResultSet rs = (ResultSet) pstmt.executeQuery();
			
			StringBuffer sb = new StringBuffer();
			sb.append("select id_usuario, count(valor) from LANCE where id_usuario in ( ");
			for (int i = 1; i < qtd ; i++) {
				sb.append(" ? ,");
			}
			sb.append(" ? ");
			sb.append(" ) ");
			sb.append(" group by id_usuario ");
			pstmt = (PreparedStatement) getConnection().prepareStatement(sb.toString());

			for (int i = 1; i < qtd + 1; i++) {
				rs.next();
				pstmt.setString(i, rs.getString(1));
			}
			
			rs = (ResultSet) pstmt.executeQuery();
			
			System.out.println("      ULTIMOS " + qtd);
			while(rs.next()){
				System.out.println(rs.getString(1) + " - " + rs.getInt(2));
			}
			System.out.println("      FIM ULTIMOS" + qtd);
			
			
			rs.close();
			pstmt.close();
			fecharConexao();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		LanceDao dao = new LanceDao();
		
		dao.contabilizarUltimosLances(50);
		
	}
}
